USE master

IF EXISTS(SELECT * FROM sys.databases WHERE NAME='ITCSystem')
	DROP DATABASE ITCSystem

--CREACION DE BASE DE DATOS
CREATE DATABASE ITCSystem

USE ITCSystem


CREATE TABLE Profesor
(
	Clave int identity(1,1),
	Nombre varchar(100) ,
	Nombramiento int 
)
CREATE TABLE Instituto
(
	Nombre varchar(100) ,
	Id int identity(1,1)
)
CREATE TABLE Alumno
(
	NumeroControl int identity(1,1),
	Nombre varchar(100) ,
	Planestudios int ,
	Generacion int 
)
CREATE TABLE Generacion
(
	FechaInicio date ,
	FechaFinal date ,
	Id int identity(1,1)
)
CREATE TABLE Grupo
(
	Clave int identity(1,1),
	DiasSemana int ,
	Profesor int ,
	Aula int ,
	Materia int 
)
CREATE TABLE Nombramiento
(
	Nombre varchar(100) ,
	Horas int ,
	Id int identity(1,1)
)
CREATE TABLE PlanEstudio
(
	Clave int identity(1,1),
	Nombre varchar(100) 
)
CREATE TABLE Materia
(
	Clave int identity(1,1),
	Nombre varchar(100) ,
	Planestudios int 
)
CREATE TABLE Aula
(
	Clave int identity(1,1),
	Capacidad int 
)




GO
CREATE PROCEDURE INSERTPROFESOR
(
	@Nombre varchar(100),
	@Nombramiento int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Profesor VALUES (@Nombre,@Nombramiento)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTINSTITUTO
(
	@Nombre varchar(100)
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Instituto VALUES (@Nombre)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTALUMNO
(
	@Nombre varchar(100),
	@Planestudios int,
	@Generacion int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Alumno VALUES (@Nombre,@Planestudios,@Generacion)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTGENERACION
(
	@FechaInicio date,
	@FechaFinal date
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Generacion VALUES (@FechaInicio,@FechaFinal)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTGRUPO
(
	@DiasSemana int,
	@Profesor int,
	@Aula int,
	@Materia int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Grupo VALUES (@DiasSemana,@Profesor,@Aula,@Materia)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTNOMBRAMIENTO
(
	@Nombre varchar(100),
	@Horas int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Nombramiento VALUES (@Nombre,@Horas)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTPLANESTUDIO
(
	@Nombre varchar(100)
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO PlanEstudio VALUES (@Nombre)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTMATERIA
(
	@Nombre varchar(100),
	@Planestudios int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Materia VALUES (@Nombre,@Planestudios)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END

GO
CREATE PROCEDURE INSERTAULA
(
	@Capacidad int
	,@LastId INT OUT
)
AS
BEGIN
	INSERT INTO Aula VALUES (@Capacidad)
	SET @LastId = CAST(SCOPE_IDENTITY() AS INT)
END


GO
CREATE PROCEDURE DELETEPROFESOR
(
	@Clave int
)
AS
BEGIN
	DELETE FROM Profesor
	WHERE Clave = @Clave
END
GO
CREATE PROCEDURE DELETEINSTITUTO
(
	@Id int
)
AS
BEGIN
	DELETE FROM Instituto
	WHERE Id = @Id
END
GO
CREATE PROCEDURE DELETEALUMNO
(
	@NumeroControl int
)
AS
BEGIN
	DELETE FROM Alumno
	WHERE NumeroControl = @NumeroControl
END
GO
CREATE PROCEDURE DELETEGENERACION
(
	@Id int
)
AS
BEGIN
	DELETE FROM Generacion
	WHERE Id = @Id
END
GO
CREATE PROCEDURE DELETEGRUPO
(
	@Clave int
)
AS
BEGIN
	DELETE FROM Grupo
	WHERE Clave = @Clave
END
GO
CREATE PROCEDURE DELETENOMBRAMIENTO
(
	@Id int
)
AS
BEGIN
	DELETE FROM Nombramiento
	WHERE Id = @Id
END
GO
CREATE PROCEDURE DELETEPLANESTUDIO
(
	@Clave int
)
AS
BEGIN
	DELETE FROM PlanEstudio
	WHERE Clave = @Clave
END
GO
CREATE PROCEDURE DELETEMATERIA
(
	@Clave int
)
AS
BEGIN
	DELETE FROM Materia
	WHERE Clave = @Clave
END
GO
CREATE PROCEDURE DELETEAULA
(
	@Clave int
)
AS
BEGIN
	DELETE FROM Aula
	WHERE Clave = @Clave
END

GO
CREATE PROCEDURE SELECTPROFESOR
AS
BEGIN
	SELECT
	Clave,
	Nombre,
	Nombramiento
	FROM Profesor
END
GO
CREATE PROCEDURE SELECTINSTITUTO
AS
BEGIN
	SELECT
	Nombre,
	Id
	FROM Instituto
END
GO
CREATE PROCEDURE SELECTALUMNO
AS
BEGIN
	SELECT
	NumeroControl,
	Nombre,
	Planestudios,
	Generacion
	FROM Alumno
END
GO
CREATE PROCEDURE SELECTGENERACION
AS
BEGIN
	SELECT
	FechaInicio,
	FechaFinal,
	Id
	FROM Generacion
END
GO
CREATE PROCEDURE SELECTGRUPO
AS
BEGIN
	SELECT
	Clave,
	DiasSemana,
	Profesor,
	Aula,
	Materia
	FROM Grupo
END
GO
CREATE PROCEDURE SELECTNOMBRAMIENTO
AS
BEGIN
	SELECT
	Nombre,
	Horas,
	Id
	FROM Nombramiento
END
GO
CREATE PROCEDURE SELECTPLANESTUDIO
AS
BEGIN
	SELECT
	Clave,
	Nombre
	FROM PlanEstudio
END
GO
CREATE PROCEDURE SELECTMATERIA
AS
BEGIN
	SELECT
	Clave,
	Nombre,
	Planestudios
	FROM Materia
END
GO
CREATE PROCEDURE SELECTAULA
AS
BEGIN
	SELECT
	Clave,
	Capacidad
	FROM Aula
END


